package com.fann;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class ClassRoomDAO {
	//public static Connection conn = null;

	public static void main(String[] args) {

	}
/*
	public static Connection getConnection() {
		if (conn == null) {
			try {
				DriverManager.registerDriver(new Driver());
				conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic?useUnicode=true&characterEncoding=UTF-8","root", "");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return conn;
	}
*/
	public boolean add(ClassRoom CR) {
		
		boolean addJudge = true;
		SimpleDateFormat nowdate = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = nowdate.format(new java.util.Date());

		if (addJudge) {
			Connection conn = null;
			PreparedStatement pstmt = null;
			try {
				DriverManager.registerDriver(new Driver());
				conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic?useUnicode=true&characterEncoding=UTF-8","root", "");

				String strSql = "INSERT INTO class_room(cr_id, cr_name, cr_location, cr_cost, up_date) values (?, ?, ?, ?, ?)";
				pstmt = conn.prepareStatement(strSql);
				pstmt.setString(1, CR.cr_id);
				
				pstmt.setString(2, CR.cr_name);
				pstmt.setString(3, CR.cr_location);
				pstmt.setInt(4, CR.cr_cost);
				
				pstmt.setString(5, time);
//				int rowCount = 
						pstmt.executeUpdate();
//				System.out.println(rowCount);
			} catch (SQLException e) {
				e.printStackTrace();
				addJudge = false;
			} finally {
				try {
					if (conn != null && pstmt != null) {
						pstmt.close();
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return addJudge;
	}

	public int update(ClassRoom CR) {

		int rowCount = 0;
		SimpleDateFormat nowdate = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = nowdate.format(new java.util.Date());
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic?useUnicode=true&characterEncoding=UTF-8","root", "");

			String strSql = "update class_room set cr_id=?, cr_name=?, cr_location=?, cr_cost=?, up_date=?  where id=?";
			pstmt = conn.prepareStatement(strSql);
			pstmt.setString(1, CR.cr_id);
			pstmt.setString(2, CR.cr_name);
			pstmt.setString(3, CR.cr_location);
			pstmt.setInt(4, CR.cr_cost);
			pstmt.setString(5, time);
			pstmt.setInt(6, CR.id);
			rowCount = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowCount;
	}

	public int delete(String id) {
		int rowCount = 0;
		try {
			DriverManager.registerDriver(new Driver());
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic", "root", "");

			String sql = "delete from class_room where id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rowCount = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowCount;
	}

	public List<ClassRoom> getAll() {
		
		List<ClassRoom> CRs = new ArrayList<ClassRoom>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic","root","");
			
			String sql = "select * from class_room";
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()) {

				int id = rs.getInt("id");
				String cr_id = rs.getString("cr_id");
				String cr_name = rs.getString("cr_name");
				String cr_location = rs.getString("cr_location");
				int cr_cost = rs.getInt("cr_cost");
				ClassRoom CR = new ClassRoom(id, cr_id, cr_name, cr_location, cr_cost);
				CRs.add(CR);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return CRs;
	}

	public ClassRoom getClassRoomById(String id) {
		ClassRoom CR = null;
		
		//int rowCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic","root","");
			
			String sql = "select * from class_room where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				CR = new ClassRoom(rs.getInt("id"), rs.getString("cr_id"), rs.getString("cr_name"), rs.getString("cr_location"), rs.getInt("cr_cost"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return CR;
	}

}
